require "spreadsheet/excel"
require "tempfile"
include Spreadsheet


class ExcelsheetGeneratorController < ApplicationController
  
  before_filter :init_data
    rescue_from Exception, :with => :render_error

  def render_error(exception)
    logger.info "User logged : #{current_user.inspect}"
    log_error(exception)
    render :template => "/errors/error.html.erb"
  end

  
  def initialize
    @format_bold_silver = Spreadsheet::Format.new :weight => :bold , :pattern_fg_color => :silver,  :pattern => 1
    @format_border_silver = Spreadsheet::Format.new :pattern_fg_color => :silver,  :pattern => 1, :border => 2
    @format_border = Spreadsheet::Format.new :border => 2
    @format_border_bold = Spreadsheet::Format.new :border => 2, :weight => :bold
    @format_center_bold = Spreadsheet::Format.new(:align => :center, :weight => :bold , :border => 2)
    @format_center_border = Spreadsheet::Format.new(:align => :center, :border => 2)
    @format_merge = Spreadsheet::Format.new(:align => :merge)
    @format_merge_bold = Spreadsheet::Format.new(:align => :merge, :weight => :bold, :border => 2 )
    @format_merge_bold_silver = Spreadsheet::Format.new :align => :merge, :weight => :bold , :pattern_fg_color => :silver,  :pattern => 1, :border => 2
    @format_merge_border = Spreadsheet::Format.new :align =>:merge, :border => 2
    @format_merge_border_left = Spreadsheet::Format.new :align =>:merge, :border => 2, :horizontal_align=>:left
  end 
    
  def admin_report
    site_roles=Array.new
    country_roles=Array.new
    strategy_roles=Array.new
    @report_request.report_responses.each{|report_response|
      role=report_response.owner_role
      case report_response.owner_role.coordinated_type
        when Site.name 
          site_roles.push(report_response)
        when Country.name
          country_roles.push(report_response)
        when Strategy.name
          strategy_roles.push(report_response)
      end
    }
    
    site_roles.each{|report_response|
      self.site(report_response.owner_role,report_response)
    }
    country_roles.each{|report_response|
      self.country(report_response.owner_role,report_response)
    }
    
    #this is harcode, represent the total number of XB
    if site_roles.length>country_roles.length
      cant_max_people=site_roles.length
    else
      cant_max_people=country_roles.length
    end
    
    
    worksheet=@workbook.add_worksheet(_("Strategy Coordinator checklist"))
    widths=[7,90]
    cant_max_people.times do |i|
      widths.push(22)
    end
    init_widths(worksheet,widths)
    row=0
    line=[[_("STRATEGY COORDINATOR (Country)"),2,@format_merge],["",1],["",1],[report_period_time(@report_request),1]]
    print_row(row,worksheet,line)
    row=1
    line=[[_("Strategies and Indicators"),2+cant_max_people,@format_merge_bold]]
    print_row(row,worksheet,line)
    
    strategy_roles=strategy_roles.sort { |s1, s2| s1.owner_role.strategy.id <=> s2.owner_role.strategy.id } 
    strategy_roles.each{|report_response|
      row=self.strategy(report_response.owner_role,report_response,row,worksheet,cant_max_people)
    }
    
    if @report_request.report_responses.empty?
      worksheet=@workbook.add_worksheet("empty")
    end
    @workbook.close
    send_file @file.path, :type => 'application/vnd.ms-excel; charset=utf-8; header=present', :disposition => 'inline',:filename=> @filename
  end
  
  
   def strategy(role,report_response,row,worksheet,cant_max_people)
    row=row+1
    set_countries_of_strategy=Set.new
    countries_of_strategy=Array.new
    role.users.each{|user|
      set_countries_of_strategy.add("#{user.country.name}")
    }
    set_countries_of_strategy.each{|country_name|
      countries_of_strategy.push( _(country_name))
    }
    
    line=[["#{role.strategy.name} (#{countries_of_strategy})",2+cant_max_people,@format_bold_silver]] 
    print_row(row,worksheet,line)
    row=row+1
    line=[[_("Indicators"),2,@format_merge]]
    print_row(row,worksheet,line)
    row=row+1
    line=[["",1,@format_border],[_("Legend:  No - O     Yes - ●    Not Applicable - NA"),1,@format_center_border],[_("No/Yes"),1,@format_center_border]]
    print_row(row,worksheet,line)
    
    
    maps=ReportsController.strategy_coordinator_view(role,report_response)
    coordinate_types_question_map=maps[0]
    coordinate_types_role_map=maps[1]
    
    
    name=Strategy.name
    question_map=coordinate_types_question_map[name]
    row=write_responses_strategy_checklist(question_map,row,worksheet,name,coordinate_types_role_map[name])
    
    
    row=row+1
    name=Country.name
    line=[["",1],["",1]]
      coordinate_types_role_map[name].each{|aux_role|
      line.push(["#{aux_role.country.name}",1,@format_center_border])
    }
    print_row(row, worksheet, line)
    question_map=coordinate_types_question_map[name]
    row=write_responses_strategy_checklist(question_map,row,worksheet,name,coordinate_types_role_map[name])
    
    row=row+1
    name=Site.name
    line=[["",1],["",1]]
    coordinate_types_role_map[name].each{|aux_role|
      line.push(["XB-#{aux_role.coordinated.cross_borders.map(&:number).join(',')} #{aux_role.coordinated.name}",1,@format_center_border])
    }
    print_row(row, worksheet, line)
    question_map=coordinate_types_question_map[name]
    row=write_responses_strategy_checklist(question_map,row,worksheet,name,coordinate_types_role_map[name])
    return row
  end
  
  
  def site(role,report_response)
    worksheet=@workbook.add_worksheet(_("XB-") + role.coordinated.cross_borders.map(&:number).join(',') + " "+ role.coordinated.name[0,27])
    widths=[4,90,5,60]
    init_widths(worksheet,widths)
    row=0
    line=[[xb_info(role.site),2,@format_merge],["",1],[report_period_time(report_response.report_request),2,@format_merge]]
    print_row(row,worksheet,line)
    row=1
    line=[[_("Indicators, by Strategy"),2,@format_merge_bold],[_("Yes/No"),1,@format_center_bold],[_("Comment"),1,@format_center_bold]]
    print_row(row,worksheet,line)
    
    report_response.report.groupings.each{|group|
      row=row+1
      line=[[group.text,4,@format_bold_silver]]
      print_row(row, worksheet, line)
      group.questions_for_role(role).each {|question|
      row=row+1
        if question.is_yes_no
          write_question_yesno_xb(worksheet,row,question,report_response,role)
        else
          write_question_text_xb(worksheet,row,question,report_response,role)
        end
      }
    }
  end
  
  
  def country(role,report_response)
    worksheet=@workbook.add_worksheet(_("CC ")+ _(role.coordinated.name[0,29]))
    coordinated_xbs=report_response.owner_role.coordinated_xbs
    
    widths=[10,90,10]
    coordinated_xbs.each do |xb|
      widths.push(10)
    end
    widths.push(80)
    init_widths(worksheet,widths)
    
    row=0
    line=[[_("COUNTRY: ")+ _(role.country.name),2,@format_merge],["",1],["",1],[report_period_time(report_response.report_request),2,@format_merge]]
    print_row(row,worksheet,line)
    row=1
    line=[[_("Indicators"),2,@format_merge_bold],["",1,@format_center_bold],[_("XB sites"),coordinated_xbs.length,@format_center_bold],[_("Comments"),1,@format_center_bold]]
    print_row(row,worksheet,line)
    line=[["",1,@format_center_border],[_("Legend: No O   Partial Ө  Yes/Complete ●   Not applicable NA"),1,@format_center_border],[_("CC"),1,@format_center_border]]
    coordinated_xbs.each do |xb|
      line.push(["XB-#{xb.site.cross_borders.map(&:number).join(',')}",1,@format_center_border])
    end
    line.push([_("(Main activities for reporting period, problems identified and how they were addressed)"),1,@format_center_border])
    print_row(2,worksheet,line)
    total_columns=line.length
    row=2
    report_response.report.groupings.reject {|x| x.strategy.nil?}.each do |group|
      row=row+1
      line=Array.new
      line=[[group.text,total_columns,@format_bold_silver]]
      print_row(row, worksheet, line)
      group.questions_for_role(role).each do |question|
        line=Array.new
        row=row+1
#        yes_responses=0
        if question.is_yes_no
          cc_answer = report_response.cc_answer(question)
          if not cc_answer.nil?
            format_code=@format_center_border
          else
            format_code=@format_border
          end
          line.push([question.code,1,format_code])
          line.push([question.text_for(role),1,@format_border])
          coordinated_xbs.each do |xb|
           xb_answer = report_response.xb_answer question, xb
            if not xb_answer.nil?
              if xb_answer.is_yes
                line.push(["●",1,@format_border])
#                  yes_responses=yes_responses+1
              elsif xb_answer.is_no
                  line.push(["O",1,@format_border])
              else
                line.push(["",1,@format_border])
              end
            else            
              line.push([_("NA"),1,@format_border_silver])  
            end  
          end
          if not cc_answer.nil?
              if cc_answer.is_yes
                line.insert(2,["●",1,@format_border])
              elsif cc_answer.is_no
                  line.insert(2,["O",1,@format_border])
              else
                line.insert(2,["",1,@format_border])
            end
            line.push([cc_answer.value_text,1,@format_border])
          else
#            line.insert(2,[yes_responses,1,@format_border])
              line.insert(2,[_("NA"),1,@format_border_silver])
              line.push(["",1,@format_border])
          end
          print_row(row,worksheet,line) 
        else
          if question.is_option
            line.push([question.code,1,@format_center_border])
            line.push([question.text_for(role),1,@format_border])
            print_row(row,worksheet,line)
            row=row+1
            row=write_question_option_cc(report_response,row,question,worksheet)
          end
        end
      end
    end    
  end
  
  def write_question_option_cc(report_response,row,question,worksheet)
    answer = report_response.cc_answer question
    if not question.text_for( report_response.owner_role ).nil?
      question.options.each do |question_option|
        line=Array.new
        line=[["",1,@format_border],["#{question_option.code} - "+ _(question_option.text) ,1,@format_border]]
        if not answer.nil?
          state = answer.state_for question_option
          show_yes = state
          show_no = (not state.nil? and not state)
          if show_yes
              line.push(["●",1,@format_center_border])
          elsif show_no
            line.push(["O",1,@format_center_border])
          else
            line.push(["",1,@format_center_border])
          end
        else
          line.push([_("NA"),1,@format_border_silver])
        end
        report_response.owner_role.coordinated_xbs.each do |xb|
          xb_answer = report_response.xb_answer question, xb
          if not xb_answer.nil?
            state = xb_answer.state_for question_option
            show_yes = state
            show_no = (not state.nil? and not state)
            if show_yes
              line.push(["●",1,@format_center_border])
            elsif show_no
              line.push(["O",1,@format_center_border])
            else
              line.push(["",1,@format_center_border])
            end
          else
            line.push(["",1,@format_center_border])
          end
        end
        print_row(row, worksheet, line)
        row=row+1
      end
    end
    return row-1
  end
  
  def write_responses_strategy_checklist(question_map,row,worksheet,name,set_roles)
    questions=question_map.keys.sort{|x,y| x.id <=> y.id }
    questions.each{|question|
      role_map=question_map[question]
      if question.is_yes_no
        row=row+1
        line=[[question.code,1,@format_border_bold],[question.text_for(Role.kind(name.constantize)),1,@format_border]]
        set_roles.each{|aux_role|
          answer=role_map[aux_role]
          if not answer.nil?
            if answer.is_yes
              line.push(["●",1,@format_center_border])
            else  
              if answer.is_no
                 line.push(["O",1,@format_center_border])
              else
                line.push(["",1,@format_center_border])
              end
           end
         else
           line.push(["",1,@format_center_border])
         end
        }
       print_row(row, worksheet, line)
      end
    }
    return row
  end
    
  def init_data
    @report_request = ReportRequest.find_by_id params[:id]
    render :nothing => true, :status => 404 and return false if @report_request.nil?
    title = @report_request.title.gsub(/\s/, '_')
    title = title.gsub(/[\/~|]/, '*')
    @filename = "MBDS_#{title}.xls"
    @file=Tempfile.new @filename
    @workbook=Spreadsheet::Excel.new(@file.path)
    true
  end
  
  def xb_info(site)
    site_xb = _(site.name) +" - " +_(site.matching_names)
    country_name=site.country.name
    city_name=site.city
    _("XB SITE: ") +site_xb+"    "+_("Country")+" - "+ _(country_name)+"    "+ _("Province")+" - #{city_name}"
  end
  
  def report_period_time(report_request)
  #For 6-month period from Year - 2009         Month - June             to Year - 2009              Month - December
    year_from=report_request.period_start.year
    month_period_from=report_request.period_start.month
    year_to=report_request.period_end.year
    month_period_to=report_request.period_end.month
    month_map={1 => _("January"), 6 => _("June"), 7=> _("July"), 12 => _("December")}
    result =_("For 6-month period from Year")+" - #{year_from}    Month - #{month_map[month_period_from]}    to Year - #{year_to}    Month - #{month_map[month_period_to]}"
    return result
  end
  

  def print_row(row,worksheet,line)
begin
    column=0
    line.each{ |cell|
      if cell.length == 3
        format=cell[2]
      else
        format=(Spreadsheet::Format.new)  
      end
      if cell[1]>1
        merge_row = worksheet.row(row)
        cell[1].times do |i|
          merge_row.set_format(column+i,format)
        end
        merge_row[column] = cell[0]
      else
        worksheet.write(row,column,cell[0],format)
      end  
      column=column+cell[1]
    }
rescue
end
  end
  
  def init_widths(worksheet, widths)
    count=0     
    widths.each { |width| 
      worksheet.column(count).width = width
      count=count+1
    }
  end
  
  def write_question_yesno_xb(worksheet,row,question,report_response,role)
    line=Array.new
    line.push([question.code,1,@format_border])   
    question_text=question.text_for(role)
    line.push([question_text,1,@format_border])
    answer=report_response.answer(question)
   
    
    if answer.nil?
      line.push(["",1,@format_border],["",1,@format_border])
    else
      if answer.is_no
        line.push(["O",1,@format_border])
      elsif answer.is_yes
        line.push(["●",1,@format_border])
      else
        line.push(["",1,@format_border])
      end
      if not answer.value_text.nil?
        line.push([answer.value_text,1,@format_border])  
      else
        line.push(["",1,@format_border])
      end
    end
    print_row(row, worksheet, line)
  end
  
  
  
  def write_question_text_xb(worksheet,row,question,report_response,role)
#    worksheet.row(row).height = 30
    line=[[question.text_for(role),2,@format_merge_border]]
    answer=report_response.answer(question)
    if answer.nil?
      line.push(["-",2,@format_merge_border])
    else
      if ((not answer.value_text.nil?) && answer.value_text.length > 1 ) 
        line.push([answer.value_text,2,@format_merge_border])
      else
        line.push(["-",2,@format_merge_border])
      end
    end
    print_row(row, worksheet, line)
  end  
end